package com.wangshuqi.jdbc.preparedStatement.blob;

import com.wangshuqi.jdbc.bean.Customer;
import com.wangshuqi.jdbc.util.JDBCUtil;
import org.junit.Test;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;

/**
 * 使用preparedStatement操作MySQL中的blob数据类型
 *
 * @author codewsq
 * @date 2021/8/23 0023 20:50
 */
public class PreparedStatementBlobTest {

    /*
     * 插入blob数据，图片不大时正常操作
     *      有可能会抛出异常com.mysql.jdbc.PacketTooBigException: Packet for query is too large (8122140 > 4194304)，
     *      这是需要在mysql的配置文件里配置 max_allowed_packet=16M 属性，windows下时ini配置文件
     * */
    @Test
    public void test() {
        Connection conn = null;
        PreparedStatement ps = null;
        InputStream is = null;
        try {
            // 1.获取连接
            conn = JDBCUtil.getConnection();
            // 2.编写sql语句
            String sql = "insert into customers(name,email,birth,photo) values (?, ?, ?, ?)";
            // 3.预编译sql语句
            ps = conn.prepareStatement(sql);

            ps.setString(1, "二郎神"); // 数据库从1开始
            ps.setString(2, "erlangshen@163.com");
            ps.setObject(3, "1992-02-02");
            // blob数据类型存储的是一个输入流对象，所以这里创建一个输入流对象
//            is = new FileInputStream("姐妹.jpg"); // 文件小，未抛出如上异常
            is = new FileInputStream("C:\\Users\\wangshuqi\\Pictures\\java基础\\java集合.png"); // 大文件
            ps.setBlob(4, is);
            // 4.执行sql
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn, ps);

            try {
                if (is != null)
                    is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /*
     * 查询blob数据
     * */
    @Test
    public void testQueryBlob() {
        Connection conn = null;
        PreparedStatement ps = null;
        InputStream is = null;
        OutputStream os = null;
        try {
            // 1.获取连接
            conn = JDBCUtil.getConnection();
            // 2.编写sql语句
            String sql = "select id, name,email,birth,photo from customers where id = ?";
            // 3.预编译sql语句
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 24);
            // 4.执行sql
            ResultSet resultSet = ps.executeQuery();
            if (resultSet.next()) {
                // 方式一
                /* int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birth = resultSet.getDate(4);
                Blob photo = resultSet.getBlob(5);*/
                // 方式二： 两种方式都可以，性能上也差不多
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                Date birth = resultSet.getDate("birth");

                Blob photo = resultSet.getBlob("photo");
                is = photo.getBinaryStream();
                os = new FileOutputStream("java集合.jpg");
                int len;
                byte[] buffer = new byte[1024];
                while ((len = is.read(buffer)) != -1) {
                    os.write(buffer, 0, len);
                }
                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn, ps);

            try {
                if (is != null)
                    is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if (os != null)
                    os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }
}
